﻿using System;
using System.IO;
using System.Xml;
using System.Text;
using System.Data;
using System.Data.Common;
using System.Data.OleDb;
using System.Collections.Generic;
using System.Collections;
using System.Text.RegularExpressions;
using System.Threading;
// Based off CDC's Epi Info 7 OleDbDatabase class; see http://epiinfo.codeplex.com

namespace NutriStat.Data 
{
    public abstract class OleDbDatabase : NutriStat.Data.DbDriverBase
    {
        #region Constructor
        
        public OleDbDatabase()
        {
        }
        #endregion Constructors

        #region Native Driver Implementation        
        protected virtual OleDbParameter ConvertToNativeParameter(QueryParameter parameter)
        {
            if (parameter.DbType.Equals(DbType.Guid))
            {
                parameter.Value = new Guid(parameter.Value.ToString());
            }

            return new OleDbParameter(parameter.ParameterName, CovertToNativeDbType(parameter.DbType), parameter.Size, parameter.Direction, parameter.IsNullable, parameter.Precision, parameter.Scale, parameter.SourceColumn, parameter.SourceVersion, parameter.Value);
        }
        
        protected virtual OleDbConnection GetNativeConnection()
        {
            return GetNativeConnection(connectionString);
        }
        
        protected virtual OleDbConnection GetNativeConnection(string connectionString)
        {
            OleDbConnectionStringBuilder oleDBCnnStrBuilder = new OleDbConnectionStringBuilder(connectionString);
            //oleDBCnnStrBuilder.Provider = "Microsoft.Jet.OLEDB.4.0";
            oleDBCnnStrBuilder.Provider = "Microsoft.ACE.OLEDB.12.0";

            return new OleDbConnection(oleDBCnnStrBuilder.ToString());
        }
        
        protected OleDbCommand GetNativeCommand(IDbTransaction transaction)
        {
            OleDbTransaction oleDbtransaction = transaction as OleDbTransaction;

            #region Input Validation
            if (oleDbtransaction == null)
            {
                throw new ArgumentException("Transaction parameter must be a OleDbTransaction.", "transaction");
            }
            #endregion

            return new OleDbCommand(null, (OleDbConnection)transaction.Connection, (OleDbTransaction)transaction);
        }

        public override DataTable Select(Query selectQuery, DataTable dataTable)
        {
            #region Input Validation
            if (selectQuery == null)
            {
                throw new ArgumentNullException("selectQuery");
            }
            if (dataTable == null)
            {
                throw new ArgumentNullException("dataTable");
            }
            #endregion Input Validation

            IDbConnection connection = GetConnection();
            OleDbDataAdapter adapter = new OleDbDataAdapter();
            adapter.SelectCommand = (OleDbCommand)GetCommand(selectQuery.SqlStatement, connection, selectQuery.Parameters);

            try
            {
                //Logger.Log(selectQuery);
                adapter.Fill(dataTable);
                return dataTable;
            }
            catch (OleDbException ex)
            {
                throw new System.ApplicationException("Error executing select query against the database.", ex);
            }
            catch (Exception ex)
            {
                throw new System.ApplicationException("Error executing select query against the database.", ex);
            }
        }

        public override void Update(DataTable dataTable, string tableName, Query insertQuery, Query updateQuery)
        {

            #region Input Validation

            if (dataTable == null)
            {
                throw new ArgumentNullException("DataTable");
            }
            if (string.IsNullOrEmpty(tableName))
            {
                throw new ArgumentNullException("TableName");
            }

            #endregion Input Validation

            IDbConnection connection = GetConnection();
            OleDbDataAdapter adapter = new OleDbDataAdapter();

            if (insertQuery != null)
            {
                //Logger.Log(insertQuery);
                adapter.InsertCommand = (OleDbCommand)GetCommand(insertQuery.SqlStatement, connection, insertQuery.Parameters);
            }
            if (updateQuery != null)
            {
                //Logger.Log(updateQuery);
                adapter.UpdateCommand = (OleDbCommand)GetCommand(updateQuery.SqlStatement, connection, updateQuery.Parameters);
            }

            try
            {
                adapter.Update(dataTable);
            }
            catch (Exception ex)
            {
                throw new System.ApplicationException("Error updating data.", ex);
            }
        }

        #endregion

        #region Temporary junk pile
        protected virtual bool HasPassword(string connectionString)
        {

            if (!string.IsNullOrEmpty(connectionString))
            {
                OleDbConnectionStringBuilder builder = new OleDbConnectionStringBuilder(this.connectionString);
                return builder.ContainsKey("Jet OLEDB:Database Password");
            }
            else
            {
                return false;
            }
        }

        public override string DbName // Implements IDbDriver.DbName
        {
            get
            {
                return dbName;
            }
            set
            {
                dbName = value;
            }
        }  private string dbName = string.Empty;


        private string location;
        private string connectionString;


        protected string Location
        {
            get
            {
                return location;
            }

        }

        public override string DataSource
        {
            get
            {
                return Location;
            }
        }

        public override string ConnectionString
        {
            get
            {
                if (HasPassword(this.connectionString))
                {
                    return this.connectionString;
                }
                else
                {
                    if (this.location == null)
                    {
                        return this.connectionString;
                    }
                    else
                    {
                        return this.location;
                    }

                }
            }
            set
            {
                this.connectionString = value;
                ProcessConnectionString();
            }
        }

        public override string OleConnectionString
        {
            get
            {
                if (ConnectionString.Contains("="))
                {
                    return ConnectionString;
                }
                else
                {
                    //return "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + ConnectionString + ";";
                    return "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + ConnectionString + ";";
                }
            }
        }

        protected void
            ProcessConnectionString()
        {
            this.location = null;
            if (this.connectionString == null) return;

            OleDbConnectionStringBuilder builder = new OleDbConnectionStringBuilder(this.connectionString);

            string filename = builder.DataSource;
            if (File.Exists(filename))
            {
                if (!Path.IsPathRooted(filename))
                {
                    filename = Path.GetFullPath(filename);
                }
                this.location = filename;
                this.DbName = Path.GetFileNameWithoutExtension(this.location);
                this.connectionString = builder.ToString();
            }
        }

        protected virtual OleDbType CovertToNativeDbType(DbType dbType)
        {
            switch (dbType)
            {
                case DbType.AnsiString:
                    return OleDbType.VarChar;
                case DbType.AnsiStringFixedLength:
                    return OleDbType.Char;
                case DbType.Binary:
                    return OleDbType.Binary;
                case DbType.Boolean:
                    return OleDbType.Boolean;
                case DbType.Byte:
                    return OleDbType.UnsignedTinyInt;
                case DbType.Currency:
                    return OleDbType.Currency;
                case DbType.Date:
                    return OleDbType.DBDate;
                case DbType.DateTime:
                    return OleDbType.DBTimeStamp;
                case DbType.Decimal:
                    return OleDbType.Decimal;
                case DbType.Double:
                    return OleDbType.Double;
                case DbType.Guid:
                    return OleDbType.Guid;
                case DbType.Int16:
                    return OleDbType.SmallInt;
                case DbType.Int32:
                    return OleDbType.Integer;
                case DbType.Int64:
                    return OleDbType.BigInt;
                case DbType.Object:
                    return OleDbType.VarChar;
                case DbType.SByte:
                    return OleDbType.TinyInt;
                case DbType.Single:
                    return OleDbType.Single;
                case DbType.String:
                    return OleDbType.VarWChar;
                case DbType.StringFixedLength:
                    return OleDbType.WChar;
                case DbType.Time:
                    return OleDbType.DBTimeStamp;
                case DbType.UInt16:
                    return OleDbType.UnsignedSmallInt;
                case DbType.UInt32:
                    return OleDbType.UnsignedInt;
                case DbType.UInt64:
                    return OleDbType.UnsignedBigInt;
                case DbType.VarNumeric:
                    return OleDbType.VarNumeric;
                default:
                    return OleDbType.VarChar;
            }
        }
        
        public abstract void SetDataSourceFilePath(string filePath);

        public override Query CreateQuery(string sqlStatement)
        {
            //return new JetQuery(sqlStatement);
            return new JetQuery(sqlStatement);
        }

        #endregion Public Methods

        #region Schema and DDL Support
        public override int GetTableCount()
        {
            int tableCount = 0;

            DbConnection connection = this.GetNativeConnection();
            try
            {
                OpenConnection(connection);
                
                DataTable tables = null;

                string[] restrictions = new string[4];
                restrictions[3] = "Table";
                tables = connection.GetSchema("Tables", restrictions);

                for (int i = 0; i < tables.Rows.Count; i++)
                {
                    tableCount++;
                }
            }
            finally
            {
                CloseConnection(connection);
            }
            return tableCount;
        }

        public override List<string> GetTableColumnNames(string tableName)
        {
            DataTable table = this.GetSchema("Columns", tableName);
            List<string> list = new List<string>();
            foreach (DataRow row in table.Rows)
            {
                list.Add(row["COLUMN_NAME"].ToString());
            }
            return list;
        }

        public override bool DeleteTable(string tableName)
        {
            Query query = this.CreateQuery("Drop Table " + tableName);
            return (ExecuteNonQuery(query) > 0);
        }

        public override bool TableExists(string tableName)
        {
            #region Input Validation
            if (string.IsNullOrEmpty(tableName))
            {
                throw new ArgumentNullException("tableName");
            }
            #endregion
            DataTable table;

            OleDbConnection conn = this.GetNativeConnection();
            try
            {
                OpenConnection(conn);
                table = conn.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, new object[] { null, null, null, "TABLE" });
            }
            finally
            {
                CloseConnection(conn);
            }

            return (table.Select("TABLE_NAME = '" + tableName.Replace("'", "''") + "'").Length > 0);
        }

        public override bool ColumnExists(string tableName, string columnName)
        {
            #region Input Validation
            if (string.IsNullOrEmpty(tableName))
            {
                throw new ArgumentNullException("tableName");
            }
            if (string.IsNullOrEmpty(columnName))
            {
                throw new ArgumentNullException("columnName");
            }
            #endregion
            OleDbConnection conn = this.GetNativeConnection();

            try
            {
                OpenConnection(conn);

                object[] objTable;
                objTable = new object[] { null, null, tableName, null };
                DataTable schemaCols;
                schemaCols = conn.GetOleDbSchemaTable(OleDbSchemaGuid.Columns, objTable);
                return (schemaCols.Select("COLUMN_NAME = '" + columnName.Replace("'", "''") + "'").Length > 0);
            }
            finally
            {
                CloseConnection(conn);
            }
        }

        public override DataView GetTextColumnNames(string tableName)
        {
            return new DataView(this.GetSchema("Columns", tableName));

        }

        private DataTable GetSchema(string collectionName, string tableName)
        {
            #region Input Validation
            if (string.IsNullOrEmpty(collectionName))
            {
                throw new ArgumentNullException("collectionName");
            }
            if (string.IsNullOrEmpty(tableName))
            {
                throw new ArgumentNullException("tableName");
            }
            #endregion

            OleDbConnection conn = this.GetNativeConnection();

            try
            {
                OpenConnection(conn);
                return conn.GetSchema(collectionName, new string[] { null, null, tableName, null });
            }
            finally
            {
                CloseConnection(conn);
            }
        }

        private DataTable GetSchema(string collectionName)
        {

            OleDbConnection conn = this.GetNativeConnection();

            try
            {
                OpenConnection(conn);
                return conn.GetSchema(collectionName, new string[] { null, null, null });
            }
            finally
            {
                CloseConnection(conn);
            }
        }
        /*
        public override DataSets.TableSchema.TablesDataTable GetTableSchema()
        {
            OleDbConnection conn = this.GetNativeConnection();

            try
            {
                OpenConnection(conn);
                DataTable table = conn.GetSchema("Tables", new string[] { null, null, null, "Table" });
                DataSets.TableSchema tableSchema = new Epi.DataSets.TableSchema();
                tableSchema.Merge(table);
                return tableSchema._Tables;
            }
            finally
            {
                CloseConnection(conn);
            }
        }
        */

        public override List<string> GetTableNames()
        {            

            /*
            List<string> tableNames = new List<string>();
            DataTable schemaTable = GetTableSchema();

            foreach (DataRow row in schemaTable.Rows)
            {
                tableNames.Add(row[ColumnNames.SCHEMA_TABLE_NAME].ToString());
            }
            return tableNames;
             */

            List<string> tableNames = new List<string>();
            
            DbConnection connection = this.GetNativeConnection();
            try
            {
                OpenConnection(connection);

                //DbProviderFactory factory = DbProviderFactories.GetFactory("System.Data.OleDb");
                DataTable tables = null;

                string[] restrictions = new string[4];
                restrictions[3] = "Table";
                tables = connection.GetSchema("Tables", restrictions);

                for (int i = 0; i < tables.Rows.Count; i++)
                {
                    tableNames.Add(tables.Rows[i][2].ToString());
                }
            }
            finally
            {
                CloseConnection(connection);
            }
            return tableNames;
        }
        /*
        public override DataSets.TableKeysSchema.Primary_KeysDataTable GetTableKeysSchema(string tableName)
        {

            OleDbConnection conn = this.GetNativeConnection();

            try
            {
                OpenConnection(conn);
                DataTable tb = conn.GetOleDbSchemaTable(OleDbSchemaGuid.Primary_Keys, new object[] { null, null, tableName });
                DataSets.TableKeysSchema schema = new Epi.DataSets.TableKeysSchema();
                schema.Merge(tb);
                return schema.Primary_Keys;
            }
            finally
            {
                CloseConnection(conn);
            }
        }

        public override DataSets.TableColumnSchema.ColumnsDataTable GetTableColumnSchema(string tableName)
        {
            OleDbConnection conn = this.GetNativeConnection();

            try
            {
                OpenConnection(conn);
                DataTable dt = conn.GetOleDbSchemaTable(OleDbSchemaGuid.Columns, new object[] { null, null, tableName, null });
                DataSets.TableColumnSchema schema = new Epi.DataSets.TableColumnSchema();
                schema.Merge(dt);
                return schema.Columns;
            }
            catch (Exception ex)
            {
                throw new GeneralException("Could not get table column schema for." + tableName, ex);
            }
            finally
            {
                CloseConnection(conn);
            }
        }

        public override DataSets.ANSI.TableColumnSchema.ColumnsDataTable GetTableColumnSchemaANSI(string tableName)
        {
            OleDbConnection conn = this.GetNativeConnection();

            try
            {
                OpenConnection(conn);
                DataTable dt = conn.GetOleDbSchemaTable(OleDbSchemaGuid.Columns, new object[] { null, null, tableName, null });
                DataSets.ANSI.TableColumnSchema schema = new Epi.DataSets.ANSI.TableColumnSchema();
                schema.Merge(dt);
                return schema.Columns;
            }
            catch (Exception ex)
            {
                throw new GeneralException("Could not get table column schema for." + tableName, ex);
            }
            finally
            {
                CloseConnection(conn);
            }
        }
        */
        #endregion

        #region Generic IDbTransaction Methods (mirror Epi.Data.Office/Epi.Data.SqlServer)

        public override bool TestConnection()
        {
            try
            {
                return TestConnection(connectionString);
            }
            catch (Exception ex)
            {
                //throw new GeneralException("Could not connect to data source.", ex);
                throw new Exception("Could not connect to data source.", ex);
            }
        }

        protected bool TestConnection(string connectionString)
        {

            IDbConnection testConnection = GetConnection(connectionString);
            try
            {
                OpenConnection(testConnection);
            }
            finally
            {
                CloseConnection(testConnection);
            }

            return true;

        }

        protected virtual IDbConnection GetConnection()
        {
            return GetNativeConnection(connectionString);
        }

        protected virtual IDbConnection GetConnection(string connectionString)
        {
            return GetNativeConnection(connectionString);
        }

        protected virtual IDbCommand GetCommand(string sqlStatement, IDbTransaction transaction, List<QueryParameter> parameters)
        {

            #region Input Validation
            if (string.IsNullOrEmpty(sqlStatement))
            {
                throw new ArgumentNullException("sqlStatement");
            }
            if (parameters == null)
            {
                throw new ArgumentNullException("Parameters");
            }
            #endregion

            IDbCommand command = this.GetNativeCommand(transaction);
            command.CommandText = sqlStatement;

            foreach (QueryParameter parameter in parameters)
            {
                command.Parameters.Add(this.ConvertToNativeParameter(parameter));
            }

            return command;
        }

        protected virtual IDbCommand GetCommand(string sqlStatement, IDbConnection connection, List<QueryParameter> parameters)
        {

            #region Input Validation
            if (string.IsNullOrEmpty(sqlStatement))
            {
                throw new ArgumentNullException("sqlStatement");
            }
            if (parameters == null)
            {
                throw new ArgumentNullException("parameters");
            }
            #endregion

            IDbCommand command = connection.CreateCommand();
            command.CommandText = sqlStatement;

            foreach (QueryParameter parameter in parameters)
            {
                command.Parameters.Add(this.ConvertToNativeParameter(parameter));
            }

            return command;
        }

        protected void OpenConnection(IDbConnection conn)
        {
            try
            {
                if (conn.State != ConnectionState.Open)
                {
                    conn.Open();
                }
            }
            catch (Exception)
            {
                throw new System.ApplicationException("Error opening connection. Please check the database you are trying to open.");
            }
        }

        protected void CloseConnection(IDbConnection conn)
        {
            try
            {
                if (conn != null)
                {
                    if (conn.State != ConnectionState.Closed)
                    {
                        conn.Close();
                    }
                }
            }
            catch (Exception ex)
            {
                conn = null;
                throw new System.ApplicationException("Error closing connection.", ex);
            }
        }

        public override void CloseTransaction(IDbTransaction transaction)
        {
            if (transaction == null)
            {
                throw new ArgumentNullException("Transaction cannot be null.", "transaction");
            }

            CloseConnection(transaction.Connection);
        }

        public override IDbTransaction OpenTransaction()
        {
            IDbConnection connection = GetConnection();
            OpenConnection(connection);
            return connection.BeginTransaction();
        }

        public override IDbTransaction OpenTransaction(IsolationLevel isolationLevel)
        {
            IDbConnection connection = GetConnection();
            OpenConnection(connection);
            return connection.BeginTransaction(isolationLevel);
        }

        public override IDataReader GetTableDataReader(string tableName)
        {
            Query query = this.CreateQuery("select * from " + tableName);
            return this.ExecuteReader(query);
        }

        public override DataTable Select(Query selectQuery)
        {
            #region Input Validation
            if (selectQuery == null)
            {
                throw new ArgumentNullException("selectQuery");
            }
            #endregion

            DataTable table = new DataTable();
            return Select(selectQuery, table);

        }

        public override DataTable GetTableData(string tableName, string columnNames, string sortCriteria)
        {
            try
            {
                if (string.IsNullOrEmpty(columnNames))
                {
                    columnNames = "*";
                }
                string queryString = "select " + columnNames + " from [" + tableName + "]";
                if (!string.IsNullOrEmpty(sortCriteria))
                {
                    queryString += " order by " + sortCriteria;
                }
                Query query = this.CreateQuery(queryString);
                return Select(query);
            }
            finally
            {
            }
        }

        public override IDataReader ExecuteReader(Query selectQuery)
        {
            #region Input Validation

            if (selectQuery == null)
            {
                throw new ArgumentNullException("SelectQuery");
            }

            #endregion

            IDbCommand command = null;
            IDbConnection connection = null;

            try
            {
                connection = GetConnection();
                OpenConnection(connection);
                command = GetCommand(selectQuery.SqlStatement, connection, selectQuery.Parameters);
                    
                return command.ExecuteReader();
            }
            catch (Exception ex)
            {
                throw new System.ApplicationException("Could not execute reader", ex);
            }
        }

        public override object ExecuteScalar(Query query, IDbTransaction transaction)
        {
            #region Input Validation
            if (query == null)
            {
                throw new ArgumentNullException("query");
            }
            if (transaction == null)
            {
                throw new ArgumentNullException("transaction");
            }
            #endregion

            object result;
            IDbCommand command = GetCommand(query.SqlStatement, transaction, query.Parameters);

            try
            {
                // do not open connection, we are inside a transaction
                result = command.ExecuteScalar();
            }
            finally
            {
                // do not close connection, we are inside a transaction
            }

            return result;
        }

        public override object ExecuteScalar(Query query)
        {

            #region Input Validation
            if (query == null)
            {
                throw new ArgumentNullException("query");
            }
            #endregion

            object result;
            IDbConnection conn = GetConnection();
            IDbCommand command = GetCommand(query.SqlStatement, conn, query.Parameters);

            try
            {
                OpenConnection(conn);
                result = command.ExecuteScalar();
            }
            finally
            {
                CloseConnection(conn);
            }

            return result;
        }

        public override int ExecuteNonQuery(Query query)
        {
            #region Input Validation
            if (query == null)
            {
                throw new ArgumentNullException("query");
            }
            #endregion

            //Logger.Log(query);
            IDbConnection conn = this.GetConnection();
            IDbCommand command = GetCommand(query.SqlStatement, conn, query.Parameters);
                        
            try
            {
                OpenConnection(conn);
                return command.ExecuteNonQuery();
            }
            finally
            {
                CloseConnection(conn);
            }
        }

        public override int ExecuteNonQuery(Query query, IDbTransaction transaction)
        {

            #region Input Validation
            if (query == null)
            {
                throw new ArgumentNullException("query");
            }
            if (transaction == null)
            {
                throw new ArgumentNullException("transaction");
            }
            #endregion

            //Logger.Log(query);
            IDbCommand command = GetCommand(query.SqlStatement, transaction, query.Parameters);

            try
            {
                // do not try to open connection, we are inside a transaction
                return command.ExecuteNonQuery();
            }
            finally
            {
                // do not close connection, we are inside a transaction
            }
        }

        #endregion


        #region IDbDriver Members

        public static string EncodeOleDbConnectionStringValue(string value)
        {
            // any preceding or trailing spaces in the string value must be 
            // enclosed in quotations marks. trailing or leading spaces
            // are ignored around integer, boolean, or enumerated values 
            // even if quoted 
            string result = value.Trim();

            // if the value contans a semicolon, single-quote character, 
            // or double-quote character, the value must be enclosed in 
            // quotation marks. The quoation-mark character used to enclose the
            // value must be doubled everytime it occures within the value

            if (value.IndexOfAny(new char[] { ';', '\'', '\"' }) > -1)
            {
                result.Replace("'", "''");
                result = "'" + result + "'";
            }

            // to include an equal sign (=) in a keyword or value, 
            // it must be preceded by another equal sign
            result = value.Replace("=", "==");

            return result;
        }

        #endregion

        public override DataTable GetTableData(string tableName)
        {
            return GetTableData(tableName, string.Empty, string.Empty);
        }

        public override DataTable GetTableData(string tableName, string columnNames)
        {
            return GetTableData(tableName, columnNames, string.Empty);
        }

        public override string FullName
        {
            get { throw new Exception("The method or operation is not implemented."); }
        }

        public override string ConnectionDescription
        {
            get { throw new Exception("The method or operation is not implemented."); }
        }

        public override void CreateTable(string tableName, List<TableColumn> columns)
        {
            throw new Exception("The method or operation is not implemented.");
        }
 
        public override string IdentifyDatabase()
        {
            throw new Exception("The method or operation is not implemented.");
        }

        public override string InsertInEscape(string str)
        {
            string newString = string.Empty;            
            
            if (!str.StartsWith("["))
            {
                newString = "[";
            }
            newString += str;
            if (!str.EndsWith("]"))
            {
                newString += "]";
            }
            
            return newString;
        }

        public override string FormatDate(DateTime dt)
        {         
            return "#" + dt.ToShortDateString() + "#";
        }

    }
}

